#!/usr/bin/env python
# -*- coding: utf-8 -*-
# 时间     : 2019-12-11 15:54:52
# 作者     : DL (584054432@qq.com)
# 网址     : https://gitee.com/dingliangPython/
# 软件版本 : Python3.7.3
# 功能     ：
import logging
import os
import sqlite3

import cx_Oracle
import pymssql
import pymysql
import pyodbc
import tools
from DBUtils.PooledDB import PooledDB

os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.ZHS16GBK'


class SqlClient(object):
    """docstring for SqlClient"""

    # 初始化
    def __init__(self, *args, **kwargs):
        super(SqlClient, self).__init__()
        self.__dict__ = kwargs
        self.kwargs = kwargs
        self.args = args
        self.log = logging.getLogger('main.SqlClient')
        self.type = self.type.lower()
        self.connectPool = self.getPoolConnect()

    # 默认没有的属性为None
    def __getattr__(self, key):
        return 0

    # 回滚
    def rollback(self):
        connect = self.getCursor()
        if connect:
            connect.rollback()
            connect.close()
            self.log.warning("执行失败回滚数据...")
            return True

    # 获取Oracle ODBC 连接
    def getOracleODBCConnect(self):
        for driver in pyodbc.drivers():
            if 'oracle in ' in driver.lower():
                dsn = "Driver={{{}}};Dbq={}/{};Uid={};Pwd={};".format(driver, self.host, self.database, self.username,
                                                                      self.password)
                return pyodbc.connect(dsn, timeout = 1, autocommit = True)

    # 获取access 连接
    def getAccessConnect(self):
        if self.dsn:
            driver_str = "DSN={};UID=admin;PWD={};".format(self.dsn, self.password or '')
            return pyodbc.connect(driver_str, timeout = 1, autocommit = True)

        if not os.path.exists(self.database):
            tools.setMessage("数据库文件：{}，不存在，连接失败".format(self.database), '警告')
            os._exit(0)
        driver_list = pyodbc.drivers()
        driver_str = 'Microsoft Access Driver (*.mdb, *.accdb)'
        if driver_str not in driver_list:
            rec = tools.Set_Message("没有accdb驱动，请安装ACCESS2010或以上驱动程序,确认退出，取消继续")
            if rec == 1:
                os._exit(0)

        for driver in driver_list[::-1]:
            if driver_str != driver:
                continue
            database = self.databasepath or self.database
            _str = "DRIVER={%s};DBQ=%s;Pwd=%s;ExtendedAnsiSQL=1;" % (driver, database, self.password or "")
            try:
                return pyodbc.connect(_str, timeout = 1, autocommit = True)
            except Exception as e:
                rec = tools.setMessage("%s" % e, '异常告警')
                if rec == 1:
                    os._exit(0)

    # 获取ODBC连接
    def getDSNConnect(self):
        if self.type == 'access':
            return self.getAccessConnect()
        elif self.type == 'oracle':
            return self.getOracleODBCConnect()
        else:
            _str = "DSN={};UID={};PWD={};".format(self.dsn, self.username, self.password or '')
            if self.type in ('mssql', 'sqlserver', 'sql server'):
                SQL_ATTR_CONNECTION_TIMEOUT = 113
                login_timeout = 1
                connection_timeout = 1
                connection = pyodbc.connect(_str, timeout = login_timeout,
                                            attrs_before = {SQL_ATTR_CONNECTION_TIMEOUT: connection_timeout},
                                            autocommit = True)
                return connection
            odbc = pyodbc.connect(_str, autocommit = True, timeout = 1)
            return odbc

    # 获取连接池
    def getPoolConnect(self):
        if not self.pool:
            return
        if self.type == 'oracle':
            dsn = "{}:{}/{}".format(self.host, self.port or 1521, self.database)
            minNumber = int(self.mincached or 5)
            maxNumber = int(self.mincached or 5)
            pool = cx_Oracle.SessionPool(self.username, self.password, dsn, min = minNumber, max = maxNumber,
                                         increment = 0,
                                         threaded = True)
            return pool
        if self.type in ['mysql', 'mssql', 'sqlserver', 'sql server']:
            driver = pymysql if self.type == 'mysql' else pymssql
            port = self.port or 3306 if self.type == 'mysql' else self.port or 1433
            pool = PooledDB(
                    creator = driver,  # 使用链接数据库的模块
                    host = self.host,
                    user = self.username,
                    port = int(port),
                    password = self.password,
                    database = self.database,
                    maxconnections = int(self.maxconnections or 20),  # 连接池允许的最大连接数，0和None表示没有限制
                    mincached = int(self.mincached or 5),  # 初始化时，连接池至少创建的空闲的连接，0表示不创建
                    maxcached = int(self.mincached or 5),  # 连接池空闲的最多连接数，0和None表示没有限制
                    maxshared = int(self.maxshared or 0),  # 连接池中最多共享的连接数量，0和None表示全部共享
                    blocking = True,  # 链接池中如果没有可用共享连接后，是否阻塞等待，True表示等待，False表示不等待然后报错
                    charset = self.coding or 'utf8',
                    login_timeout = 1
                    )
            self.log.info('数据库：{}连接池初始化。。。'.format(self.type))
            print(1111111111,pool)
            return pool

    # 获取驱动连接
    def getDriveConnect(self):
        if self.type in ['mssql', 'sqlserver', 'sql server']:
            return pymssql.connect(self.host, self.username, self.password, self.database, timeout = 3,
                                   port = int(self.port or 1433))
        if self.type == 'mysql':
            return pymysql.connect(self.host, self.username, self.password, self.database, port = int(self.port),
                                   charset = 'utf8')
        if self.type == 'oracle':
            dsn = "{}:{}/{}".format(self.host, self.port or 1521, self.database)
            return cx_Oracle.connect(self.username, self.password, dsn)
        if self.type == 'sqlite':
            if not os.path.exists(self.database):
                tools.setMessage("数据库文件：{}，不存在，连接失败".format(self.database), '警告')
                os._exit(0)
            return sqlite3.connect(self.database, self.timeout, check_same_thread = False)
        if self.type == 'access':
            return self.getAccessConnect()

    # 获取连接
    def getConnect(self):
        if self.dsn:
            return self.getDSNConnect()
        elif self.pool and self.type not in ['access', 'sqlite']:
            return self.connectPool
        else:
            return self.getDriveConnect()

    # 获取连接游标
    def getCursor(self):
        try:
            if self.pool and self.type == 'oracle':
                conn = self.connectPool.acquire()

            elif self.pool and self.type not in ['access', 'sqlite']:
                conn = self.connectPool.connection()
            else:
                conn = self.getConnect()
            return conn
        except Exception as errorInfo:
            self.log.warning("连接异常:{}".format(errorInfo))

    # 查询数据
    def loadSql(self, sql, returnDict = None):
        try:
            conn = self.getCursor()
            cursor = conn.cursor()
            cursor.execute(sql)
            value = cursor.fetchall()
            data = []
            if value and len(value) >= 1:
                # 返回元组
                if not returnDict:
                    return value
                # 返回字典
                field = tuple([d[0] for d in cursor.description])
                for v in value:
                    data.append(dict(zip(field, v)))
                conn.commit()
                cursor.close()
                return data
            else:
                conn.commit()
                cursor.close()
                return 0
        except Exception as errorInfo:
            if "NoneType" in str(errorInfo):
                self.log.warning("数据库主机:{},ODBC:{},库或实例或文件:{},连接失败,请检测连接".format(self.host, self.dsn, self.database))
                return
            self.log.warning("查询出错:{},出错语句:{}".format(errorInfo, sql))

    # 执行 插入，更新，删除等操作语句
    def execSql(self, sql, value_list = None):
        try:
            conn = self.getCursor()
            cursor = conn.cursor()
            if not value_list:
                cursor.execute(sql)
                result = cursor.rowcount
                conn.commit()
                cursor.close()
                return result
            else:
                cursor.executemany(sql, value_list)
                result = cursor.rowcount
                conn.commit()
                cursor.close()
                return result
        except Exception as errorInfo:
            errorInfo = str(errorInfo)
            if "ORA-00001" in errorInfo:
                self.log.warning("主键冲突,请检测主键是否重复")
                return
            if "NoneType" in errorInfo:
                self.log.warning("数据库主机:{},ODBC:{},库或实例或文件:{},连接失败,请检测连接".format(self.host, self.dsn, self.database))
                return
            self.log.warning("执行出错:{},出错语句:{}".format(errorInfo, sql))


# @tools.try_error
def Get_Connect_List(config):
    obj = tools.obj()
    for _, v in config.items():
        name = v.get('id') or v.get('name')
        _type = v.get('type')
        if name and _type:
            obj.__dict__.update({name: SqlClient(**v)})
    return obj


if __name__ == '__main__':
    import loadConfig, time_ext_
    from concurrent.futures import ThreadPoolExecutor

    config = loadConfig.loadIni(r'D:\CODE\PYTHON\Tem\config\config.ini').read()
    conn = Get_Connect_List(config)
    sql = "SELECT SYSDATE XX FROM  DUAL"
    sql = "select date()"
    sql = "insert into t_rl_czpmain (gid,addtime)values ('1213444',sysdate)"


    def run(n):
        r = conn.CICS.execSql(sql)
        print('线程:{},值:{}\n'.format(n, r))


    s = time_ext_.time()
    with ThreadPoolExecutor(max_workers = 5) as aa:
        for i in range(5):
            aa.submit(run, (i))
    print(time_ext_.time() - s)
    time_ext_.sleep(10)